import sqlite3
import json
from typing import List, Dict, Optional
from datetime import datetime
import os


class Database:
    
    def __init__(self, db_path: str = "data/companion.db"):
        os.makedirs(os.path.dirname(db_path), exist_ok=True)
        
        self.db_path = db_path
        self.conn = sqlite3.connect(db_path, check_same_thread=False)
        self.conn.row_factory = sqlite3.Row
        self.create_tables()
    
    def create_tables(self):
        cursor = self.conn.cursor()
        
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS players (
                player_id INTEGER PRIMARY KEY,
                nickname TEXT NOT NULL,
                games TEXT NOT NULL,
                voice_type TEXT,
                price REAL CHECK(price >= 5 AND price <= 100),
                rating REAL,
                tags TEXT,
                description TEXT,
                is_new BOOLEAN,
                response_time INTEGER,
                accept_rate REAL,
                online BOOLEAN,
                total_orders INTEGER,
                is_blocked BOOLEAN,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ''')
        
        # 用户表
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS users (
                user_id INTEGER PRIMARY KEY,
                username TEXT,
                favorite_games TEXT,
                preferred_voice TEXT,
                avg_price REAL,
                total_orders INTEGER,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ''')
        
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS orders (
                order_id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER,
                player_id INTEGER,
                game TEXT,
                price REAL,
                duration REAL,
                rating REAL,
                order_time TIMESTAMP,
                FOREIGN KEY (user_id) REFERENCES users(user_id),
                FOREIGN KEY (player_id) REFERENCES players(player_id)
            )
        ''')
        
        cursor.execute('CREATE INDEX IF NOT EXISTS idx_players_online ON players(online)')
        cursor.execute('CREATE INDEX IF NOT EXISTS idx_players_price ON players(price)')
        cursor.execute('CREATE INDEX IF NOT EXISTS idx_orders_user ON orders(user_id)')
        cursor.execute('CREATE INDEX IF NOT EXISTS idx_orders_player ON orders(player_id)')
        
        self.conn.commit()
    
    def insert_player(self, player: Dict):
        cursor = self.conn.cursor()
        
        if 'price' in player:
            player['price'] = max(5, min(100, player['price']))
        
        cursor.execute('''
            INSERT OR REPLACE INTO players 
            (player_id, nickname, games, voice_type, price, rating, tags, 
             description, is_new, response_time, accept_rate, online, total_orders, is_blocked)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ''', (
            player['player_id'],
            player['nickname'],
            json.dumps(player['games'], ensure_ascii=False),
            player['voice_type'],
            player['price'],
            player['rating'],
            json.dumps(player['tags'], ensure_ascii=False),
            player['description'],
            player['is_new'],
            player['response_time'],
            player['accept_rate'],
            player['online'],
            player['total_orders'],
            player['is_blocked']
        ))
        self.conn.commit()
    
    def insert_user(self, user: Dict):
        """插入用户数据"""
        cursor = self.conn.cursor()
        cursor.execute('''
            INSERT OR REPLACE INTO users 
            (user_id, username, favorite_games, preferred_voice, avg_price, total_orders)
            VALUES (?, ?, ?, ?, ?, ?)
        ''', (
            user['user_id'],
            user['username'],
            json.dumps(user['favorite_games'], ensure_ascii=False),
            user['preferred_voice'],
            user['avg_price'],
            user['total_orders']
        ))
        self.conn.commit()
    
    def insert_order(self, order: Dict):
        """插入订单数据"""
        cursor = self.conn.cursor()
        cursor.execute('''
            INSERT INTO orders 
            (user_id, player_id, game, price, duration, rating, order_time)
            VALUES (?, ?, ?, ?, ?, ?, ?)
        ''', (
            order['user_id'],
            order['player_id'],
            order['game'],
            order['price'],
            order['duration'],
            order['rating'],
            order['order_time']
        ))
        self.conn.commit()
    
    def get_all_players(self) -> List[Dict]:
        """获取所有陪玩师"""
        cursor = self.conn.cursor()
        cursor.execute('SELECT * FROM players WHERE is_blocked = 0')
        
        players = []
        for row in cursor.fetchall():
            player = dict(row)
            player['games'] = json.loads(player['games'])
            player['tags'] = json.loads(player['tags'])
            players.append(player)
        
        return players
    
    def get_all_users(self) -> List[Dict]:
        """获取所有用户"""
        cursor = self.conn.cursor()
        cursor.execute('SELECT * FROM users')
        
        users = []
        for row in cursor.fetchall():
            user = dict(row)
            user['favorite_games'] = json.loads(user['favorite_games'])
            users.append(user)
        
        return users
    
    def get_all_orders(self) -> List[Dict]:
        """获取所有订单"""
        cursor = self.conn.cursor()
        cursor.execute('SELECT * FROM orders')
        return [dict(row) for row in cursor.fetchall()]
    
    def update_player_online_status(self, player_id: int, online: bool):
        """更新陪玩师在线状态"""
        cursor = self.conn.cursor()
        cursor.execute('''
            UPDATE players 
            SET online = ?, updated_at = CURRENT_TIMESTAMP 
            WHERE player_id = ?
        ''', (online, player_id))
        self.conn.commit()
    
    def get_online_players(self) -> List[Dict]:
        """获取在线陪玩师"""
        cursor = self.conn.cursor()
        cursor.execute('SELECT * FROM players WHERE online = 1 AND is_blocked = 0')
        
        players = []
        for row in cursor.fetchall():
            player = dict(row)
            player['games'] = json.loads(player['games'])
            player['tags'] = json.loads(player['tags'])
            players.append(player)
        
        return players
    
    def close(self):
        """关闭数据库连接"""
        self.conn.close()



